require 'csv'

class GlobalizeMigration < ActiveRecord::Migration

  def self.up
    create_table :globalize_countries, :force => true do |t|
      t.column :code,               :string, :limit => 2
      t.column :english_name,       :string
      t.column :date_format,        :string
      t.column :currency_format,    :string
      t.column :currency_code,      :string, :limit => 3
      t.column :thousands_sep,      :string, :limit => 2
      t.column :decimal_sep,        :string, :limit => 2
      t.column :currency_decimal_sep,        :string, :limit => 2
      t.column :number_grouping_scheme,      :string
    end
    add_index :globalize_countries, :code

    create_table :globalize_translations, :force => true do |t|
      t.column :type,           :string
      t.column :tr_key,         :string
      t.column :table_name,     :string
      t.column :item_id,        :integer
      t.column :facet,          :string
      t.column :language_id,    :integer
      t.column :pluralization_index, :integer
      t.column :text,           :text
      t.column :namespace,      :string
    end

    add_index :globalize_translations, [ :tr_key, :language_id ]
    add_index :globalize_translations, [ :table_name, :item_id, :language_id ], :name => 'globalize_translations_table_name_and_item_and_language'

    create_table :globalize_languages, :force => true do |t|
      t.column :iso_639_1, :string, :limit => 2
      t.column :iso_639_2, :string, :limit => 3
      t.column :iso_639_3, :string, :limit => 3
      t.column :rfc_3066,  :string
      t.column :english_name, :string
      t.column :english_name_locale, :string
      t.column :english_name_modifier, :string
      t.column :native_name, :string
      t.column :native_name_locale, :string
      t.column :native_name_modifier, :string
      t.column :macro_language, :boolean
      t.column :direction, :string
      t.column :pluralization, :string
      t.column :scope, :string, :limit => 1
    end

    add_index :globalize_languages, :iso_639_1
    add_index :globalize_languages, :iso_639_2
    add_index :globalize_languages, :iso_639_3
    add_index :globalize_languages, :rfc_3066

    # add in defaults
    load_from_csv("globalize_countries", country_data)
    load_from_csv("globalize_languages", language_data)
    #load_from_csv_file("globalize_translations", translation_data)
  end

  def self.down
    drop_table :globalize_countries
    drop_table :globalize_translations
    drop_table :globalize_languages
  end

  def self.load_from_csv(table_name, data)
    column_clause = nil
    is_header = false
    cnx = ActiveRecord::Base.connection
    ActiveRecord::Base.silence do
      reader = CSV::Reader.create(data)

      columns = reader.shift.map {|column_name| cnx.quote_column_name(column_name) }
      column_clause = columns.join(', ')

      reader.each do |row|
        next if row.first.nil? # skip blank lines
        raise "No table name defined" if !table_name
        raise "No header defined" if !column_clause
        values_clause = row.map {|v| cnx.quote(v).gsub('\\n', "\n").gsub('\\r', "\r") }.join(', ')
        sql = "INSERT INTO #{table_name} (#{column_clause}) VALUES (#{values_clause})"
        cnx.insert(sql)
      end
    end
  end

  def self.country_data
    <<'END_OF_DATA'
"id","code","english_name","date_format","currency_format","currency_code","thousands_sep","decimal_sep","currency_decimal_sep","number_grouping_scheme"
1,"DE","Germany",,"%n €","EUR",".",",",",","western"
2,"ES","Spain",,"%n €","EUR",".",",",",","western"
3,"US","United States of America",,,"USD",",",".",".","western"
END_OF_DATA
  end

  def self.language_data
    <<'END_OF_DATA'
"id","iso_639_1","iso_639_2","iso_639_3","rfc_3066","english_name","english_name_locale","english_name_modifier","native_name","native_name_locale","native_name_modifier","macro_language","direction","pluralization","scope"
1556,"de","deu","deu",,"German",,,"Deutsch",,,0,"ltr","c == 1 ? 1 : 2","L"
1819,"en","eng","eng",,"English",,,,,,0,"ltr","c == 1 ? 1 : 2","L"
5889,"es","spa","spa",,"Spanish",,,"Español",,,0,"ltr","c == 1 ? 1 : 2","L"
END_OF_DATA
  end

  def self.translation_data
    <<'END_OF_DATA'
"id","type","tr_key","table_name","item_id","facet","language_id","text","pluralization_index"
END_OF_DATA
  end
end
